/****** Object:  StoredProcedure [dbo].[factSalesPosted]    Script Date: 4.5.2021 08:26:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Kristinn Thor Sigurjonsson
-- Create date: 2017-07-18
-- Modified date: 2020-11-23
-- Modified Date: 2021-04-13 Use Merge to eliminate risk of duplicate rows
-- Description:	SP with option of Incremental load for fSalesPosted
-- =============================================
ALTER PROCEDURE [dbo].[factSalesPosted] 

	@TransSalesEntryRowID bigint, 
	@IncoiveLineRowID bigint,
	@CreditMemolineRowID bigint,
	@TransSalesEntryNewRowID bigint, 
	@IncoiveLineNewRowID bigint,
	@CreditMemolineNewRowID bigint
	
AS
BEGIN
SET NOCOUNT ON;

IF EXISTS (SELECT
		*
	FROM sys.key_constraints
	WHERE name = 'PK_DW.FactSalesPosted')
ALTER TABLE [DW].[FactSalesPosted] DROP CONSTRAINT [PK_DW.FactSalesPosted];


WITH

/*
Get the list of companies used
*/
tCompanies
AS
(SELECT
		dCOM.[SK_Company] as [Company]
	   ,dCOM.[CompanyPrefix]
	FROM [DW].[dCompany] dCOM
	WHERE dCOM.[SK_Company] <> -1),
/*
Set up dimensions and support tables used with the main data process
*/
tLocation
AS
(SELECT
		dLOC.[SK_location]
	   ,tCOM.[CompanyPrefix]
	   ,dLOC.[LocationCode]
	FROM DW.[dLocation] dLOC
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dLOC.[Company] = tCOM.[Company]),

tItem
AS
(SELECT
		dITM.[SK_Item]
	   ,tCOM.[CompanyPrefix]
	   ,dITM.[No]
	FROM [DW].[dItem] dITM
	LEFT OUTER JOIN tCompanies tCOM
		ON dITM.[Company] = tCOM.[Company]),

tMember
AS
(SELECT
		dMEM.[SK_Member]
	   ,dMEM.[CardNo]
	   ,dMEM.[ContactNo]
	   ,tCOM.[CompanyPrefix]
	   ,tCOM.Company
	FROM [DW].[dMember] dMEM
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dMEM.[Company] =tCOM.[Company]),

tMembershipCard
AS
(SELECT
		dMSC.[SK_MembershipCard]
	   ,dMSC.[CardNo]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dMembershipCard] dMSC
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dMSC.Company = tCOM.Company),

tPOSTerminal
AS
(SELECT
		dPOT.[SK_POSTerminal]
	   ,dPOT.[No]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dPOSTerminal] dPOT
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dPOT.[Company] = tCOM.[Company]),

tStaff
AS
(SELECT
		dSTA.[SK_Staff]
	   ,dSTA.[ID]
	   ,dSTA.[SourceTable]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dStaff] dSTA
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dSTA.[Company] = tCOM.[Company]),

tCustomer
AS
(SELECT
		dCUS.[SK_Customer]
	   ,dCUS.[CustomerNo]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dCustomer] dCUS
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dCUS.Company = tCOM.Company),

tSalesDocument
AS
(SELECT
		dSDO.[SK_SalesDocument]
	   ,dSDO.[No]
	   ,dSDO.[DocumentType]
	   ,dSDO.[StoreNo]
	   ,dSDO.[TransactionNo]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dSalesDocument] dSDO
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dSDO.[Company] = tCOM.[Company]),

tGlobalDimension1
AS
(SELECT
		dGD1.[SK_GlobalDimension1]
	   ,dGD1.[Code]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dGlobalDimension1] dGD1
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dGD1.[Company] = tCOM.[Company]),

tGlobalDimension2
AS
(SELECT
		dGD2.[SK_GlobalDimension2]
	   ,dGD2.[Code]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dGlobalDimension2] dGD2
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dGD2.[Company] = tCOM.[Company]),

tItemCostAdjustment
AS
(SELECT
		fICA.[Item No_]
	   ,fICA.[Location Code]
	   ,fICA.[Variant Code]
	   ,fICA.[ValidFromDate]
	   ,fICA.[ValidToDate]
	   ,tCOM.[CompanyPrefix]
	   ,fICA.[WeightedItemAverageCost]
	FROM [DW].[fItemCostAdjustment] fICA
	LEFT OUTER JOIN [tCompanies] tCOM
		ON fICA.[Company] = tCOM.[Company]),

tBusinessPostingGroup
AS
(SELECT
		dBPG.[SK_BusinessPostingGroup]
	   ,dBPG.[Code]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dBusinessPostingGroup] dBPG
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dBPG.[Company] = tCOM.[Company]),

tProductPostingGroup
AS
(SELECT
		dPPG.[SK_ProductPostingGroup]
	   ,dPPG.[Code]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dProductPostingGroup] dPPG
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dPPG.[Company] = tCOM.[Company]),

tStore
AS
(SELECT
		dSTO.[SK_Store]
	   ,dSTO.[StoreNo]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dStore] dSTO
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dSTO.[Company] = tCOM.[Company])
/*
Get and link data from TransSalesEntry
*/
,
tFactSalesPosted
AS
(SELECT
		sTSE.[Line No_] AS [LineNo]
	   ,CAST(sTSE.[Transaction No_] AS NVARCHAR(100)) AS [TransactionNo]
	   ,tCOM.[Company] AS [SK_Company]
	   ,0 AS [ValueEntryNo]
	   ,1 AS [ItemLedgerEntryType]
	   ,ISNULL(tSDT.[SK_SalesDocument], -1) AS [SK_Document]
	   ,ISNULL(tLOC.[SK_Location], -1) AS [SK_Location]
	   ,sTSE.[Store No_] AS [Location Code]
	   ,sTSE.[Variant Code]
	   ,CAST(sTSE.[Date] AS DATE) AS [Posting Date]
	   ,CAST(DATEADD(mi, DATEDIFF(mi, 0, sTSE.[Time]), 0) AS DATETIME) AS [Time]
	   ,ISNULL(tITM.[SK_Item], -1) AS [SK_Item]
	   ,ISNULL(tMEM.[SK_Member], -1) AS [SK_Member]
	   ,ISNULL(tMSC.[SK_MembershipCard], -1) AS [SK_MembershipCard]
	   ,ISNULL(tSTA.[SK_Staff], -1) AS [SK_Staff]
	   ,ISNULL(tCUS.[SK_Customer], -1) AS [SK_SellToCustomer]
	   ,ISNULL(tPOT.[SK_POSTerminal], -1) AS [SK_POSTerminal]
	   ,ISNULL(tGD1.[SK_GlobalDimension1], -1) [SK_Dimension1]
	   ,ISNULL(tGD2.[SK_GlobalDimension2], -1) [SK_Dimension2]
	   ,-1 AS [SK_BusinessPostingGroup]
	   ,-1 AS [SK_ProductPostingGroup]
	   ,-1 AS [SK_LineType]
	   ,NULL AS [ShipmentDate]
	   ,-1 AS [SK_VariantCode]
	   ,-1 AS [SK_Barcode]
	   ,sTSE.[VAT Code] AS [VATCode]
	   ,sTSE.[Member Points] AS [MemberPoints]
	   ,sTSE.[Quantity] * -1 AS [Quantity]
	   ,sTSE.[Net Amount] * -1 AS [NetSalesAmountLCY]
	   ,sTSE.[VAT Amount] * -1 AS [VATAmountLCY]
	   ,(sTSE.[VAT Amount] + sTSE.[Net Amount]) * -1 AS [GrossSalesAmountLCY]
	   ,sTSE.[Cost Amount] * -1 AS [CostAmountLCY]
	   ,ISNULL(tICA.[WeightedItemAverageCost] * sTSE.[Quantity] * -1, sTSE.[Cost Amount] * -1) AS [AdjustedCostAmountLCY]
	   ,sTSE.[Discount Amount] * -1 AS [DiscountAmountLCY]
	   ,sTSE.[Replication Counter] AS [ReplicationCounter]
	   ,sTSE.[bigint_timestamp] AS [RowID]
	   ,1 AS [CurrencyFactor]
	   ,'TSE' AS [DataSource]
	FROM [dbo].[stg$Trans_ Sales Entry] sTSE
	LEFT OUTER JOIN [dbo].[stg$Transaction Header] sTRH 
		ON sTSE.[Store No_] = sTRH.[Store No_]
		AND sTSE.[POS Terminal No_] = sTRH.[POS Terminal No_]
		AND sTSE.[Transaction No_] = sTRH.[Transaction No_]
		AND sTSE.[CompanyPrefix] = sTRH.[CompanyPrefix]
	LEFT OUTER JOIN [dbo].[stg$Sales Type] sSAT  
		ON sTSE.[Sales Type] = sSAT.[Code]
		AND sTSE.[CompanyPrefix] = sSAT.[CompanyPrefix]
	LEFT OUTER JOIN [tStore] tSTO
		ON sTSE.[CompanyPrefix] = tSTO.[CompanyPrefix]
		AND sTSE.[Store No_] = tSTO.[StoreNo]
	LEFT OUTER JOIN [tLocation] tLOC --L
		ON sTSE.[CompanyPrefix] =tLOC.[CompanyPrefix]
		AND sTSE.[Store No_] = tLOC.[LocationCode]
	LEFT OUTER JOIN [tItem] tITM --I
		ON sTSE.[CompanyPrefix] =  tITM.[CompanyPrefix]
		AND sTSE.[Item No_] =  tITM.[No]
	LEFT OUTER JOIN tCompanies tCOM
		ON sTSE.[CompanyPrefix] = tCOM.[CompanyPrefix]
	LEFT OUTER JOIN [tMember] tMEM --M
		ON sTRH.[CompanyPrefix] = tMEM.[CompanyPrefix]
		AND sTRH.[Member Card No_] = tMEM.[CardNo]
	LEFT OUTER JOIN [tMembershipCard] tMSC --MSC
		ON sTRH.[CompanyPrefix] = tMSC.[CompanyPrefix]
		AND sTRH.[Member Card No_] = tMSC.[CardNo]
	LEFT OUTER JOIN [tPOSTerminal] tPOT --PT
		ON sTSE.[CompanyPrefix] = tPOT.[CompanyPrefix]
		AND sTSE.[POS Terminal No_] = tPOT.[No]
	LEFT OUTER JOIN [tStaff] tSTA --ST
		ON sTSE.[CompanyPrefix] = tSTA.[CompanyPrefix]
		AND sTSE.[Staff ID] = tSTA.[ID]
		AND tSTA.[SourceTable] = 'Staff' --added
	LEFT OUTER JOIN [tCustomer] tCUS --STC
		ON sTSE.[CompanyPrefix] = tCUS.[CompanyPrefix]
		AND sTSE.[Customer No_] = tCUS.[CustomerNo]
	LEFT OUTER JOIN [tSalesDocument] tSDT--SDT
		ON sTSE.[CompanyPrefix] = tSDT.[CompanyPrefix]
		AND sTSE.[Receipt No_] = tSDT.[No]
		AND sTSE.[Store No_] = tSDT.[StoreNo]
		AND '9' = tSDT.[DocumentType]
		AND sTSE.[Transaction No_] = tSDT.[TransactionNo]
	LEFT OUTER JOIN [tGlobalDimension1] tGD1 --GD1
		ON sTSE.[CompanyPrefix] = tGD1.[CompanyPrefix]
		AND sTSE.[Sales Type] = tGD1.[Code]
	LEFT OUTER JOIN [tGlobalDimension2] tGD2 --GD2
		ON sTSE.[CompanyPrefix] = tGD2.[CompanyPrefix]
		AND sTSE.[Sales Type] = tGD2.[Code]
	LEFT OUTER JOIN [tItemCostAdjustment] tICA --ICA
		ON sTSE.[Variant Code] = tICA.[Variant Code]
		AND sTSE.[Item No_] = tICA.[Item No_]
		AND sTSE.[Date] BETWEEN tICA.[ValidFromDate] AND tICA.[ValidToDate]
		AND sTSE.[Store No_] = tICA.[Location Code]
		AND sTSE.[CompanyPrefix] = tICA.[CompanyPrefix]

	WHERE sTRH.[Post as Shipment] = 0
	AND sTSE.[bigint_timestamp] > @TransSalesEntryRowID
	AND sTSE.[bigint_timestamp] <= @TransSalesEntryNewRowID

	/*
	Union With and process data from SalesInvoiceLine
	*/

	UNION ALL

	SELECT
		sSIL.[Line No_] AS [LineNo]
	   ,CAST(sSIL.[Document No_] AS NVARCHAR(100)) AS [TransactionNo]
	   ,tCOM.[Company] AS [SK_Company]
	   ,0 AS [ValueEntryNo]
	   ,1 AS [ItemledgerEntrytype]
	   ,ISNULL(tSDT.[SK_SalesDocument], -1) AS [SK_Document]
	   ,ISNULL(tLOC.[SK_Location], -1) AS [SK_Location]
	   ,sSIL.[Location Code]
	   ,sSIL.[Variant Code]
	   ,CAST(sSIL.[Posting Date] AS DATE) AS [Posting Date]
	   ,CAST(NULL AS DATETIME) AS [Time]
	   ,ISNULL(tITM.[SK_Item], -1) AS [SK_Item]
	   ,ISNULL(tMEM.[SK_Member], -1) AS [SK_Member]
	   ,ISNULL(tMSC.[SK_MembershipCard], -1) AS [SK_MembershipCard]
	   ,ISNULL(tSTA.[SK_Staff], -1) AS [SK_Staff]
	   ,ISNULL(tCUS.[SK_Customer], -1) AS [SK_SellToCustomer]
	   ,-1 AS [SK_POSTerminal] --? why is this set as -1 if there is a join below?
	   ,ISNULL(tGD1.[SK_GlobalDimension1], -1) AS [SK_Dimension1]
	   ,ISNULL(tGD2.[SK_GlobalDimension2], -1) AS [SK_Dimension2]
	   ,ISNULL(tBPG.[SK_BusinessPostingGroup], -1) AS [SK_BusinessPostingGroup]
	   ,ISNULL(tPPG.[SK_ProductPostingGroup], -1) AS [SK_ProductPostingGroup]
	   ,-1 AS [SK_LineType]
	   ,CAST(sSIL.[Shipment Date] AS DATE) AS [Shipment Date]
	   ,-1 AS [SK_VariantCode]
	   ,-1 AS [SK_Barcode]
	   ,'N/A' AS [VATCode]
	   ,sSIL.[Member Points] AS [MemberPoints]
	   ,sSIL.[Quantity]
	   ,(sSIL.[Amount] - sSIL.[Inv_ Discount Amount]) /
		CASE
			WHEN sSIH.[Currency Factor] = NULL THEN 1
			WHEN sSIH.[Currency Factor] = 0 THEN 1
			ELSE sSIH.[Currency Factor]
		END AS [NetSalesAmountLCY]
	   ,(sSIL.[Amount Including VAT] - sSIL.[Amount]) /
		CASE
			WHEN sSIH.[Currency Factor] = NULL THEN 1
			WHEN sSIH.[Currency Factor] = 0 THEN 1
			ELSE sSIH.[Currency Factor]
		END AS [VATAmount]
	   ,(sSIL.[Amount Including VAT] - sSIL.[Inv_ Discount Amount]) /
		CASE
			WHEN sSIH.[Currency Factor] = NULL THEN 1
			WHEN sSIH.[Currency Factor] = 0 THEN 1
			ELSE sSIH.[Currency Factor]
		END AS [GrossSalesAmountLCY]
	   ,(sSIL.[Quantity] * sSIL.[Unit Cost (LCY)]) AS [CostAmountLCY]
	   ,COALESCE(tICA.[WeightedItemAverageCost] * sSIL.[Quantity] * -1, (sSIL.[Quantity] * sSIL.[Unit Cost (LCY)])) AS [AdjustedCostAmountLCY]
	   ,(sSIL.[Line Discount Amount] + sSIL.[Inv_ Discount Amount]) /
		CASE
			WHEN sSIH.[Currency Factor] = NULL THEN 1
			WHEN sSIH.[Currency Factor] = 0 THEN 1
			ELSE sSIH.[Currency Factor]
		END AS [DiscountAmountLCY]
	   ,0 AS [ReplicationCounter]
	   ,sSIL.[bigint_timestamp] AS [RowID]
	   ,sSIH.[Currency Factor] AS [CurrencyFactor]
	   ,'SIL' AS [DataSource]
	FROM [dbo].[stg$Sales Invoice Line] sSIL --SIL
	LEFT OUTER JOIN [stg$Sales Invoice Header] sSIH --SILH
		ON sSIL.[CompanyPrefix] = sSIH.[CompanyPrefix] --Þetta var ekki bætti við
		AND sSIL.[Document No_] = sSIH.[No_]
	LEFT OUTER JOIN tLocation tLOC --L
		ON sSIL.[CompanyPrefix] = tLOC.[CompanyPrefix]
		AND sSIH.[Store No_] = tLOC.[LocationCode]
	LEFT OUTER JOIN [tCompanies] tCOM --C
		ON sSIL.[CompanyPrefix] = tCOM.[CompanyPrefix]
	LEFT OUTER JOIN [tItem] tITM --I
		ON sSIL.[CompanyPrefix] = tITM.[CompanyPrefix]
		AND sSIL.[No_] = tITM.[No]
	LEFT OUTER JOIN [tCustomer] tCUS --STC
		ON sSIL.[CompanyPrefix] = tCUS.[CompanyPrefix]
		AND sSIL.[Sell-to Customer No_] = tCUS.[CustomerNo]
	LEFT OUTER JOIN tBusinessPostingGroup tBPG --BPG
		ON sSIL.[CompanyPrefix] = tBPG.[CompanyPrefix]
		AND sSIL.[Gen_ Bus_ Posting Group] = tBPG.[Code]
	LEFT OUTER JOIN [tProductPostingGroup] tPPG --PPG
		ON sSIL.[CompanyPrefix] = tPPG.[CompanyPrefix]
		AND sSIL.[Gen_ Prod_ Posting Group] = tPPG.[Code]
	LEFT OUTER JOIN [tGlobalDimension1] tGD1 --GD1
		ON sSIL.[CompanyPrefix] = tGD1.[CompanyPrefix]
		AND sSIL.[Shortcut Dimension 1 Code] = tGD1.[Code]
	LEFT OUTER JOIN [tGlobalDimension2] tGD2 --GD2
		ON sSIL.[CompanyPrefix] = tGD2.[CompanyPrefix]
		AND sSIL.[Shortcut Dimension 2 Code] = tGD2.[Code]
	LEFT OUTER JOIN [tMember] tMEM --M
		ON sSIL.[CompanyPrefix] = tMEM.[CompanyPrefix]
		AND sSIH.[Member Card No_] = tMEM.[CardNo]
	LEFT OUTER JOIN [tMembershipCard] tMSC--MS
		ON sSIL.[CompanyPrefix] = tMSC.[CompanyPrefix]
		AND sSIH.[Member Card No_] = tMSC.[CardNo]
	LEFT OUTER JOIN [tPOSTerminal] tPOT --PT
		ON sSIL.[CompanyPrefix] = tPOT.[CompanyPrefix]
		AND sSIH.[POS ID] = tPOT.[No]
	LEFT OUTER JOIN [tSalesDocument] tSDT--SDT
		ON sSIL.[CompanyPrefix] = tSDT.[CompanyPrefix]
		AND sSIL.[Document No_] = tSDT.[No]
		AND sSIH.[Store No_] = tSDT.[StoreNo]
		AND 2 = tSDT.[DocumentType]
	LEFT OUTER JOIN [tStaff] tSTA --ST
		ON sSIL.[CompanyPrefix] = tSTA.[CompanyPrefix]
		AND sSIH.[Salesperson Code] = tSTA.[ID]
		AND tSTA.[SourceTable] = 'Sales'
	LEFT OUTER JOIN [tItemCostAdjustment] tICA --ICA
		ON sSIL.[Variant Code] = tICA.[Variant Code]
		AND sSIL.[No_] = tICA.[Item No_]
		AND sSIL.[Posting Date] BETWEEN tICA.[ValidFromDate] AND tICA.[ValidToDate]
		AND sSIL.[Location Code] = tICA.[Location Code]
		AND sSIL.[CompanyPrefix] = tICA.[CompanyPrefix]

	WHERE sSIL.[Type] = 2
	AND sSIL.[bigint_timestamp] > @IncoiveLineRowID
	AND sSIL.[bigint_timestamp] <= @IncoiveLineNewRowID

	/*
	Union With and process data from Credit Memo Line
	*/

	UNION ALL

	SELECT
		sCML.[Line No_] AS [LineNo]
	   ,CAST(sCML.[Document No_] AS NVARCHAR(100)) AS [TransactionNo]
	   ,tCOM.[Company] AS [SK_Company]
	   ,0 AS [ValueEntryNo]
	   ,1 AS [ItemledgerEntrytype]
	   ,tSDT.[SK_SalesDocument] AS [SK_Document]
	   ,tLOC.[SK_Location]
	   ,sCML.[Location Code]
	   ,sCML.[Variant Code]
	   ,CAST(sCML.[Posting Date] AS DATE) [Posting Date]
	   ,CAST(NULL AS DATETIME) AS [Time]
	   ,ISNULL(tITM.[SK_Item], -1) AS [SK_Item]
	   ,ISNULL(tMEM.[SK_Member], -1) AS [SK_Member]
	   ,ISNULL(tMSC.[SK_MembershipCard], -1) AS [SK_MembershipCard]
	   ,ISNULL(tSTA.[SK_Staff], -1) AS [SK_Staff]
	   ,ISNULL(tCUS.[SK_Customer], -1) AS [SK_SellToCustomer]
	   ,ISNULL(tPOT.[SK_POSTerminal], -1) AS[ SK_POSTerminal]
	   ,ISNULL(tGD1.[SK_GlobalDimension1], -1) AS [SK_Dimension1]
	   ,ISNULL(tGD2.[SK_GlobalDimension2], -1) AS [SK_Dimension2]
	   ,ISNULL(tBPG.[SK_BusinessPostingGroup], -1) AS [SK_BusinessPostingGroup]
	   ,ISNULL(tPPG.[SK_ProductPostingGroup], -1) AS [SK_ProductPostingGroup]
	   ,-1 AS [SK_LineType]
	   ,CAST(sCML.[Shipment Date] AS DATE) [Shipment Date]
	   ,-1 AS [SK_VariantCode]
	   ,-1 AS [SK_Barcode]
	   ,'N/A' AS [VATCode]
	   ,sCML.[Member Points] AS [MemberPoints]
	   ,sCML.[Quantity]
	   ,(sCML.[Amount] - sCML.[Inv_ Discount Amount]) /
		CASE
			WHEN sCMH.[Currency Factor] = NULL THEN 1
			WHEN sCMH.[Currency Factor] = 0 THEN 1
			ELSE sCMH.[Currency Factor]
		END AS [NetSalesAmountLCY]
	   ,(sCML.[Amount Including VAT] - sCML.[Amount]) /
		CASE
			WHEN sCMH.[Currency Factor] = NULL THEN 1
			WHEN sCMH.[Currency Factor] = 0 THEN 1
			ELSE sCMH.[Currency Factor]
		END AS [VATAmount]
	   ,(sCML.[Amount Including VAT] - sCML.[Inv_ Discount Amount]) /
		CASE
			WHEN sCMH.[Currency Factor] = NULL THEN 1
			WHEN sCMH.[Currency Factor] = 0 THEN 1
			ELSE sCMH.[Currency Factor]
		END AS [GrossSalesAmountLCY]
	   ,sCML.[Quantity] * sCML.[Unit Cost (LCY)] AS [CostAmountLCY]
	   ,ISNULL(tICA.[WeightedItemAverageCost] * sCML.[Quantity], (sCML.[Quantity] * sCML.[Unit Cost (LCY)])) AS [AdjustedCostAmountLCY]
	   ,(sCML.[Line Discount Amount] + sCML.[Inv_ Discount Amount]) /
		CASE
			WHEN sCMH.[Currency Factor] = NULL THEN 1
			WHEN sCMH.[Currency Factor] = 0 THEN 1
			ELSE sCMH.[Currency Factor]
		END AS [DiscountAmountLCY]
	   ,0 AS [ReplicationCounter]
	   ,sCML.[bigint_timestamp] AS [RowID]
	   ,sCMH.[Currency Factor] AS [CurrencyFactor]
	   ,'SCM' AS [DataSource]
	FROM [dbo].[stg$Sales Cr_Memo Line] sCML--SCM
	LEFT JOIN [stg$Sales Cr_Memo Header] sCMH --sCMH
		ON sCML.[CompanyPrefix] = sCMH.[CompanyPrefix]--added this
		AND sCML.[Document No_] = sCMH.[No_]
	LEFT OUTER JOIN [tLocation] tLOC--L
		ON sCML.[CompanyPrefix] =tLOC.[CompanyPrefix]
		AND sCMH.[Store No_] =tLOC.LocationCode
	LEFT OUTER JOIN tCompanies tCOM --C
		ON sCML.[CompanyPrefix] = tCOM.[CompanyPrefix]
	LEFT OUTER JOIN [tItem] tITM --I
		ON sCML.[CompanyPrefix] = tITM.[CompanyPrefix] --added this
		AND sCML.[No_] = tITM.[No]
	LEFT OUTER JOIN [tCustomer] tCUS --STC
		ON sCML.[CompanyPrefix] = tCUS.[CompanyPrefix]
		AND sCMH.[Sell-to Customer No_] = tCUS.[CustomerNo]
	LEFT OUTER JOIN [tBusinessPostingGroup] tBPG --BPG
		ON sCML.[CompanyPrefix] = tBPG.[CompanyPrefix]
		AND sCML.[Gen_ Bus_ Posting Group] = tBPG.[Code]
	LEFT OUTER JOIN [tProductPostingGroup] tPPG --PPG
		ON sCML.[CompanyPrefix] = tPPG.[CompanyPrefix]
		AND sCML.[Gen_ Prod_ Posting Group] = tPPG.[Code]
	LEFT OUTER JOIN [tGlobalDimension1] tGD1 --GD1
		ON sCML.[CompanyPrefix] = tGD1.[CompanyPrefix]
		AND sCML.[Shortcut Dimension 1 Code] = tGD1.[Code]
	LEFT OUTER JOIN [tGlobalDimension2] tGD2 --GD2
		ON sCML.[CompanyPrefix] = tGD2.[CompanyPrefix]
		AND sCML.[Shortcut Dimension 2 Code] = tGD2.[Code]
	LEFT OUTER JOIN [tMember] tMEM--M
		ON sCML.[CompanyPrefix] = tMEM.[CompanyPrefix]
		AND sCMH.[Member Card No_] = tMEM.[CardNo]
	LEFT OUTER JOIN [tMembershipCard] tMSC --MS
		ON sCML.[CompanyPrefix] = tMSC.[CompanyPrefix]
		AND sCMH.[Member Card No_] = tMSC.[CardNo]
	LEFT OUTER JOIN [tPOSTerminal] tPOT --PT
		ON sCML.[CompanyPrefix] = tPOT.[CompanyPrefix]
		AND sCMH.[POS ID] = tPOT.[No]
	LEFT OUTER JOIN [tSalesDocument] tSDT --SDT
		ON sCML.[CompanyPrefix] = tSDT.[CompanyPrefix]
		AND sCML.[Document No_] = tSDT.[No]
		AND 4 = tSDT.[DocumentType]
		AND sCMH.[Store No_] = tSDT.[StoreNo]
	LEFT OUTER JOIN [tStaff] tSTA --ST
		ON sCML.[CompanyPrefix] = tSTA.[CompanyPrefix]
		AND sCMH.[Salesperson Code] = tSTA.[ID]
		AND tSTA.[SourceTable] = 'Sales'
	LEFT OUTER JOIN [tItemCostAdjustment] tICA --ICA
		ON sCML.[Variant Code] = tICA.[Variant Code]
		AND sCML.[No_] = tICA.[Item No_]
		AND sCMH.[Posting Date] BETWEEN tICA.[ValidFromDate] AND tICA.[ValidToDate]
		AND sCML.[Location Code] = tICA.[Location Code]
		AND sCML.[CompanyPrefix] = tICA.[CompanyPrefix]

	WHERE sCML.[Type] <> 0
	AND sCML.[bigint_timestamp] > @CreditMemolineRowID
	AND sCML.[bigint_timestamp] <= @CreditMemolineNewRowID)

, tFactSalesPostedRanked as ( SELECT
		*
	   ,DENSE_RANK() OVER (PARTITION BY [LineNo], [TransactionNo], [ValueEntryNo], [SK_Company], [DataSource], [Location Code], [SK_Document], [SK_POSTerminal] ORDER BY [RowID] DESC) AS [Rank]
	FROM tFactSalesPosted)

MERGE [DW].[FactSalesPosted] AS Target USING (SELECT
		[LineNo]
	   ,[TransactionNo]
	   ,[ValueEntryNo]
	   ,[ItemLedgerEntryType]
	   ,[SK_Document]
	   ,COALESCE([SK_Company],-1) AS [Company]
	   ,[SK_Location]
	   ,[Location Code]
	   ,[Variant Code]
	   ,[Time]
	   ,[Posting Date]
	   ,[SK_Item]
	   ,[SK_Member]
	   ,[SK_MembershipCard]
	   ,[SK_Staff]
	   ,[SK_SellToCustomer]
	   ,[SK_POSTerminal]
	   ,[SK_Dimension1]
	   ,[SK_Dimension2]
	   ,[SK_BusinessPostingGroup]
	   ,[SK_ProductPostingGroup]
	   ,[SK_LineType]
	   ,[ShipmentDate]
	   ,[SK_VariantCode]
	   ,[SK_Barcode]
	   ,[VATCode]
	   ,[MemberPoints]
	   ,[Quantity]
	   ,[NetSalesAmountLCY]
	   ,[VATAmountLCY]
	   ,[GrossSalesAmountLCY]
	   ,[CostAmountLCY]
	   ,[AdjustedCostAmountLCY]
	   ,[DiscountAmountLCY]
	   ,[ReplicationCounter]
	   ,[RowID]
	   ,[CurrencyFactor]
	   ,[DataSource]
	FROM tFactSalesPostedRanked
	WHERE [Rank] = 1
	 ) AS Source
ON Target.[LineNo] = Source.[LineNo]
	AND Target.[TransactionNo] = Source.[TransactionNo]
	AND Target.[ValueEntryNo] = Source.[ValueEntryNo]
	AND Target.[SK_Company] = Source.[Company]
	AND Target.[DataSource] = Source.[DataSource]
	AND Target.[Location Code] = Source.[Location Code]
	AND Target.[SK_POSTerminal] = Source.SK_POSTerminal
	AND Target.[SK_Document] = Source.[SK_Document]
WHEN MATCHED
	THEN UPDATE
		SET [ItemLedgerEntryType] = Source.[ItemLedgerEntryType]
		   ,[SK_Document] = Source.[SK_Document]
		   ,[SK_Location] = Source.[SK_Location]
		   ,[Location Code] = Source.[Location Code]
		   ,[Variant Code] = Source.[Variant Code]
		   ,[Time] = Source.[Time]
		   ,[Posting Date] = Source.[Posting Date]
		   ,[SK_Item] = Source.[SK_Item]
		   ,[SK_Member] = Source.[SK_Member]
		   ,[SK_MembershipCard] = Source.[SK_MembershipCard]
		   ,[SK_Staff] = Source.[SK_Staff]
		   ,[SK_SellToCustomer] = Source.[SK_SellToCustomer]
		   ,[SK_POSTerminal] = Source.[SK_POSTerminal]
		   ,[SK_Dimension1] = Source.[SK_Dimension1]
		   ,[SK_Dimension2] = Source.[SK_Dimension2]
		   ,[SK_BusinessPostingGroup] = Source.[SK_BusinessPostingGroup]
		   ,[SK_ProductPostingGroup] = Source.[SK_ProductPostingGroup]
		   ,[SK_LineType] = Source.[SK_LineType]
		   ,[ShipmentDate] = Source.[ShipmentDate]
		   ,[SK_VariantCode] = Source.[SK_VariantCode]
		   ,[SK_Barcode] = Source.[SK_Barcode]
		   ,[VATCode] = Source.[VATCode]
		   ,[MemberPoints] = Source.[MemberPoints]
		   ,[Quantity] = Source.[Quantity]
		   ,[NetSalesAmountLCY] = Source.[NetSalesAmountLCY]
		   ,[VATAmountLCY] = Source.[VATAmountLCY]
		   ,[GrossSalesAmountLCY] = Source.[GrossSalesAmountLCY]
		   ,[CostAmountLCY] = Source.[CostAmountLCY]
		   ,[AdjustedCostAmountLCY] = Source.[AdjustedCostAmountLCY]
		   ,[DiscountAmountLCY] = Source.[DiscountAmountLCY]
		   ,[ReplicationCounter] = Source.[ReplicationCounter]
		   ,[RowID] = Source.[RowID]
		   ,[CurrencyFactor] = Source.[CurrencyFactor]
		   
WHEN NOT MATCHED BY TARGET
	THEN INSERT ([LineNo]
		, [TransactionNo]
		, [ValueEntryNo]
		, [ItemLedgerEntryType]
		, [SK_Document]
		, [SK_Company]
		, [SK_Location]
		, [Location Code]
		, [Variant Code]
		, [Time]
		, [Posting Date]
		, [SK_Item]
		, [SK_Member]
		, [SK_MembershipCard]
		, [SK_Staff]
		, [SK_SellToCustomer]
		, [SK_POSTerminal]
		, [SK_Dimension1]
		, [SK_Dimension2]
		, [SK_BusinessPostingGroup]
		, [SK_ProductPostingGroup]
		, [SK_LineType]
		, [ShipmentDate]
		, [SK_VariantCode]
		, [SK_Barcode]
		, [VATCode]
		, [MemberPoints]
		, [Quantity]
		, [NetSalesAmountLCY]
		, [VATAmountLCY]
		, [GrossSalesAmountLCY]
		, [CostAmountLCY]
		, [AdjustedCostAmountLCY]
		, [DiscountAmountLCY]
		, [ReplicationCounter]
		, [RowID]
		, [CurrencyFactor]
		, [DataSource])
			VALUES (Source.[LineNo], Source.[TransactionNo], Source.[ValueEntryNo], Source.[ItemLedgerEntryType], Source.[SK_Document], Source.[Company], Source.[SK_Location], Source.[Location Code], Source.[Variant Code], Source.[Time], Source.[Posting Date], Source.[SK_Item], Source.[SK_Member], Source.[SK_MembershipCard], Source.[SK_Staff], Source.[SK_SellToCustomer], Source.[SK_POSTerminal], Source.[SK_Dimension1], Source.[SK_Dimension2], Source.[SK_BusinessPostingGroup], Source.[SK_ProductPostingGroup], Source.[SK_LineType], Source.[ShipmentDate], Source.[SK_VariantCode], Source.[SK_Barcode], Source.[VATCode], Source.[MemberPoints], Source.[Quantity], Source.[NetSalesAmountLCY], Source.[VATAmountLCY], Source.[GrossSalesAmountLCY], Source.[CostAmountLCY], Source.[AdjustedCostAmountLCY], Source.[DiscountAmountLCY], Source.[ReplicationCounter], Source.[RowID], Source.[CurrencyFactor], Source.[DataSource]);

SELECT
	'RowCount' = @@rowcount
;
ALTER TABLE [DW].[FactSalesPosted] ADD CONSTRAINT [PK_DW.FactSalesPosted] PRIMARY KEY NONCLUSTERED
(
[LineNo] ASC,
[TransactionNo] ASC,
[ValueEntryNo] ASC,
[SK_Company] ASC,
[RowID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

END